﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using SARS.DAO.Facades;
using SARS.Entity;

namespace SARS.DAO
{
    public class ProgramDAO : IProgramDAO
    {
        private readonly SqlConnection _conn = new SqlConnection(Utils.SarConnectionString);
        private SqlCommand _cmd;
        private SqlDataReader _dr;

        public List<ProgramEntity> RetrieveProgrammeAll()
        {
            var programmeEntityList = new List<ProgramEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT * FROM [dbo].[Programmes]", _conn);
                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var programmeEntity = new ProgramEntity
                    {
                        ProgramID = Int32.Parse(_dr["ProgramID"].ToString()),
                        ProgramName = _dr["ProgramName"].ToString(),
                        Description = _dr["Description"].ToString(),
                    };

                    programmeEntityList.Add(programmeEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Programme] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return programmeEntityList;
        }

        public List<ProgramEntity> RetrieveProgramByStudentId(int studentId)
        {
            var programmeEntityList = new List<ProgramEntity>();

            try
            {
                _conn.Open();

                _cmd =
                    new SqlCommand(
                        "SELECT * FROM [Programmes] A INNER JOIN [StudentProgram] B ON A.ProgramID = B.ProgramID AND B.StudentID=@studentId",
                        _conn);
                _cmd.Parameters.Add(new SqlParameter("@studentId", studentId));
                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var programmeEntity = new ProgramEntity
                    {
                        ProgramID = Int32.Parse(_dr["ProgramID"].ToString()),
                        ProgramName = _dr["ProgramName"].ToString(),
                        Description = _dr["Description"].ToString(),
                    };

                    programmeEntityList.Add(programmeEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Programme] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return programmeEntityList;
        }

        public ProgramEntity RetrieveProgramById(int id)
        {
            var programEntity = new ProgramEntity();

            try
            {
                _conn.Open();

                _cmd =
                    new SqlCommand(
                        "SELECT * FROM [dbo].[Programmes] WHERE [ProgramID] = @ProgramID",
                        _conn);

                _cmd.Parameters.AddWithValue("@ProgramID", id);

                _dr = _cmd.ExecuteReader(CommandBehavior.SingleRow);

                while (_dr.HasRows && _dr.Read())
                {
                    programEntity.ProgramID = Int32.Parse(_dr["ProgramID"].ToString());
                    programEntity.ProgramName = _dr["ProgramName"].ToString();
                    programEntity.Description = _dr["Description"].ToString();
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Programe] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return programEntity;
        }

        public List<ProgramEntity> GetProgramsByTutorId(int tutorId)
        {
            var programmeEntityList = new List<ProgramEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("Select * from [Programmes] Where [ProgramID] IN " +
                                      "(Select [ProgramID] From [Courses] WHERE [CourseID] IN " +
                                      "(Select CourseID FROM [Sessions] WHERE [ManagerID] = @tutorId))", _conn);
                _cmd.Parameters.AddWithValue("@tutorId", tutorId);

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var programmeEntity = new ProgramEntity
                    {
                        ProgramID = Int32.Parse(_dr["ProgramID"].ToString()),
                        ProgramName = _dr["ProgramName"].ToString(),
                        Description = _dr["Description"].ToString(),
                    };

                    programmeEntityList.Add(programmeEntity);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine(string.Format("[Programme] Exception {0}", ex.Message));
            }
            finally
            {
                _conn.Close();
            }

            return programmeEntityList;
        }
    }
}